﻿Imports MySql.Data
Imports System.IO
Imports System.Data

Public Class edithelpdesk
    Dim sql As MySqlConnection
    Dim idkey As String
    Dim ipconnect As String
    Dim usernamedb As String
    Dim dbname As String
    Dim mysqlpass As String
    Dim id_user As String
    Dim position_user As String

    Public Sub New(ByRef mysql_pass As String, ByRef ip_connect As String, ByRef user_namedb As String, ByRef db_name As String)
        InitializeComponent()
        mysqlpass = mysql_pass
        ipconnect = ip_connect
        usernamedb = user_namedb
        dbname = db_name
    End Sub
    Private Sub edithelpdesk_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        sql = New MySqlConnection
        sql.ConnectionString = "server=" + ipconnect + ";user id=" + usernamedb + ";password=" + mysqlpass + ";database=" + dbname + ";Character Set =utf8;"
        Try
            sql.Open()
            ' MsgBox("CONNECTED TO DATABASE")
        Catch ex As Exception
            MsgBox("Can't Connect to database" + ex.Message)
            Me.Close()
        End Try

        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader
        Dim count As String

        If sql.State = ConnectionState.Closed Then
            sql.Open()
        End If


        mySqlCommand.CommandText = "SELECT * FROM helpdesk order by idhelpdesk;"
        mySqlCommand.Connection = sql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader

            ListViewhelp2.Items.Clear()

            While (mySqlReader.Read())

                With ListViewhelp2.Items.Add(mySqlReader("idhelpdesk"))
                    .subitems.add(mySqlReader("type_problem"))
                    .subitems.add(mySqlReader("agencies"))
                    .subitems.add(mySqlReader("state_problem"))
                    .subitems.add(mySqlReader("problem"))
                    .subitems.add(mySqlReader("date"))
                    .subitems.add(mySqlReader("edit_problem"))
                    .subitems.add(mySqlReader("officer"))
                End With
            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        sql.Close()
    End Sub
    Private Sub showdataedit()
        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader
        Dim key As String
        Dim count As Integer
        count = 0
        If sql.State = ConnectionState.Closed Then
            sql.Open()
        End If

        mySqlCommand.CommandText = "SELECT * FROM helpdesk where idhelpdesk ;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = sql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader

            ListViewhelp2.Items.Clear()

            While (mySqlReader.Read())

                With ListViewhelp2.Items.Add(mySqlReader("idhelpdesk"))
                    .subitems.add(mySqlReader("type_problem"))
                    .subitems.add(mySqlReader("agencies"))
                    .subitems.add(mySqlReader("state_problem"))
                    .subitems.add(mySqlReader("problem"))
                    .subitems.add(mySqlReader("date"))
                    .subitems.add(mySqlReader("edit_problem"))
                    .subitems.add(mySqlReader("officer"))

                End With
            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        sql.Close()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim NextForm As helpdesk = New helpdesk(mysqlpass, ipconnect, usernamedb, dbname)
        NextForm.Show()
        Me.Hide()
    End Sub

    Private Sub btnsaveedit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsaveedit.Click
        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader
        Dim size As String
        Dim condition As String
        Dim respone As Object
        If sql.State = ConnectionState.Closed Then
            sql.Open()
        End If

        Dim commandText2 As String
        respone = MsgBox("ยืนยันข้อมูลถูกต้อง by โตน ลูกพี่อารี", MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")

        If respone = 1 Then
            Try
                commandText2 = "UPDATE helpdesk SET name_person = '" & txtnameedit.Text & "'  , agencies = '" & ComboBoxagenedit.Text & "', type_problem = '" & ComboBoxtypeproedit.Text & "', location = '" & txtlocatedit.Text & "', problem = '" & txtproedit.Text & "' , officer = '" & ComboBoxoffedit.Text & "'  , state_problem = '" & ComboBoxstatedit.Text & "' , edit_problem = '" & txteditproedit.Text & "'  WHERE idhelpdesk = " & txtidedit.Text & "; "
                mySqlCommand.CommandText = commandText2
                mySqlCommand.CommandType = CommandType.Text
                mySqlCommand.Connection = sql
                mySqlCommand.ExecuteNonQuery()


            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End If
        showdataedit()

    End Sub
    Private Sub ListViewhelp2_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListViewhelp2.Click
        idkey = ListViewhelp2.SelectedItems(0).SubItems(0).Text
        If sql.State = ConnectionState.Closed Then
            sql.Open()
        End If
        Dim mysqlcommand As New MySqlCommand
        Dim mysqladeptor As New MySqlDataAdapter
        Dim mysqlreader As MySqlDataReader

        mysqlcommand.CommandText = " SELECT * FROM helpdesk where idhelpdesk = '" & idkey & "' ;"
        mysqlcommand.Connection = sql
        mysqladeptor.SelectCommand = mysqlcommand

        Try
            mysqlreader = mysqlcommand.ExecuteReader
            While (mysqlreader.Read())
                txtidedit.Text = mysqlreader("idhelpdesk")
                txtnameedit.Text = mysqlreader("name_person")
                'txtphoneedit.Text = mysqlreader("phone_person")
                txtlocatedit.Text = mysqlreader("location")
                txtproedit.Text = mysqlreader("problem")
                txteditproedit.Text = mysqlreader("edit_problem")
                ComboBoxagenedit.Text = mysqlreader("agencies")
                ComboBoxoffedit.Text = mysqlreader("officer")
                ComboBoxstatedit.Text = mysqlreader("state_problem")
                ComboBoxtypeproedit.Text = mysqlreader("type_problem")
            End While




        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        sql.Close()
    End Sub
End Class